﻿using log4net;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Windows.Forms;

[assembly: log4net.Config.XmlConfigurator(Watch = true)]
namespace ReportingApp
{
    public partial class Form1 : Form
    {
        private const string DEFAULT_DATABASE_CONNECTION_VALUE = @"Data Source=localhost;Initial Catalog=AdmSekolah;Persist Security Info=True;User ID=sa;Password=123";
        private string _databaseConnection;
        private bool _binding = false;
        private DateTime _lastUpdatedPeta = DateTime.MinValue;

        public Form1()
        {
            Log.Info("REPORTING APP STARTED");
            InitializeComponent();

            try
            {
                _databaseConnection = AppConfig.ReadSetting(AppConfig.DATABASE_KEY);
            }
            catch (Exception ex)
            {
                Log.Error(ex, ex.Message);
                AppConfig.AddUpdateAppSettings(AppConfig.DATABASE_KEY, DEFAULT_DATABASE_CONNECTION_VALUE);
                _databaseConnection = AppConfig.ReadSetting(AppConfig.DATABASE_KEY);
            }

            textBox1.Text = _databaseConnection;
        }

        private void bind()
        {
            if (_binding)
            {
                return;
            }

            try
            {
                _binding = true;
                DataSet ds = new DataSet();
                string sql, sqlSebaran;
                string titleSebaran, title1, title2, text1, text2, text3, text4, text5;
                if (rbDatang.Checked)
                {
                    sql = "sp_card_report_harian '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "', 'DATANG'";
                    sqlSebaran = "sp_card_sebaran '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "', 'DATANG'";
                    label1.Text = "Hadir Sesuai Waktu";
                    label2.Text = "Terlambat";
                    label3.Text = "Belum Hadir";
                    title1 = "Grafik 1 : Kinerja Keadaan Waktu Hadir Pegawai";
                    title2 = "Grafik 2 : Kinerja Ketepatan Waktu Kehadiran Pegawai";
                    titleSebaran = "Grafik 3 : Sebaran Waktu Hadir Pegawai";
                    text1 = "Hadir";
                    text2 = "Terlambat";
                    text3 = "Belum Hadir";
                    text4 = "Tepat Waktu";
                    text5 = "Tidak Sesuai Waktu";
                }
                else
                {
                    sql = "sp_card_report_harian '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "', 'PULANG'";
                    sqlSebaran = "sp_card_sebaran '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "', 'PULANG'";
                    label1.Text = "Pulang Sesuai Waktu";
                    label2.Text = "Pulang Sebelum Waktu";
                    label3.Text = "Belum Pulang";
                    title1 = "Grafik 1 : Kinerja Keadaan Waktu Pulang Pegawai";
                    title2 = "Grafik 2 : Akumulasi Kehadiran Pegawai";
                    titleSebaran = "Grafik 3 : Sebaran Waktu Pulang Pegawai";
                    text1 = "Pulang Sesuai Waktu";
                    text2 = "Pulang Sebelum Waktu";
                    text3 = "Belum Pulang";
                    text4 = "Pegawai Hadir Pulang";
                    text5 = "Pegawai Hadir belum Pulang";
                }
                using (var da = new SqlDataAdapter(sql, _databaseConnection))
                {
                    da.Fill(ds);

                    panelDatang.Controls.Clear();
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            var x = new AbsenDetail();
                            if (ds.Tables[0].Rows[i]["FOTO"] != DBNull.Value)
                            {
                                var ms = new MemoryStream((byte[])(ds.Tables[0].Rows[i]["FOTO"]));
                                x.pictureBox1.BackgroundImage = Image.FromStream(ms);
                            }
                            else
                            {
                                x.pictureBox1.BackgroundImage = x.pictureBox1.ErrorImage;
                            }
                            x.label1.Text = ds.Tables[0].Rows[i]["NAMA"].ToString();
                            x.label2.Text = ds.Tables[0].Rows[i]["JAM"].ToString();
                            panelDatang.Controls.Add(x);
                        }
                    }

                    panelTerlambat.Controls.Clear();
                    if (ds.Tables[1].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
                        {
                            panelTerlambat.Controls.Add(new Label() { Text = ds.Tables[1].Rows[i]["NAMA"].ToString() + Environment.NewLine + ds.Tables[1].Rows[i]["JAM"].ToString() + Environment.NewLine + Environment.NewLine, AutoSize = true });
                        }
                    }

                    panelBelumDatang.Controls.Clear();
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            panelBelumDatang.Controls.Add(new Label() { Margin = new Padding(2,2,2,2), AutoSize=true, Text = ds.Tables[2].Rows[i]["NAMA"].ToString() + Environment.NewLine });
                        }
                    }

                    chart1.Series[0].Points.Clear();
                    chart1.Titles[0].Text = title1;
                    double[] yValues = { ds.Tables[0].Rows.Count, ds.Tables[1].Rows.Count, ds.Tables[2].Rows.Count };
                    string[] xNames = { text1, text2, text3 };
                    chart1.Series[0].Points.DataBindXY(xNames, yValues);
                    Color[] colores = new Color[] { Color.Green, Color.LimeGreen, Color.Orange };
                    chart1.PaletteCustomColors = colores;

                    chart2.Series[0].Points.Clear();
                    chart2.Titles[0].Text = title2;
                    double[] yValues2 = { ds.Tables[0].Rows.Count, ds.Tables[1].Rows.Count + ds.Tables[2].Rows.Count };
                    string[] xNames2 = { text4, text5 };
                    chart2.Series[0].Points.DataBindXY(xNames2, yValues2);
                    Color[] colores2 = new Color[] { Color.Green, Color.Orange };
                    chart2.PaletteCustomColors = colores2;

                   
                }

                using (var da = new SqlDataAdapter(sqlSebaran, _databaseConnection))
                {
                    var dt = new DataTable();
                    da.Fill(dt);
                    chart3.Series[0].Points.Clear();
                    if (dt.Rows.Count > 0)
                    {
                        chart3.Titles[0].Text = titleSebaran;
                        foreach (DataRow row in dt.Rows)
                        {
                            var point = chart3.Series[0].Points.Add((int)row["total"]);
                            point.Label = row["jam"].ToString();
                        }
                        Color[] colores3 = new Color[] { Color.Green, Color.LimeGreen, Color.Orange };
                        chart3.PaletteCustomColors = colores3;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, ex.Message);
                //MessageBox.Show(ex.Message);
            }
            finally
            {
                _binding = false;
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            if (Screen.AllScreens.Length > 1)
            {
                Location = Screen.AllScreens[1].WorkingArea.Location;
            }
                        
            dateTimePicker1.Value = DateTime.Now;
            dateTimePicker2.Value = DateTime.Now;
            dateTimePicker3.Value = DateTime.Now;
            dateTimePicker4.Value = DateTime.Now;
            bind();
            button1_Click(null, null);
            tabControl1.SelectedIndex = 1;
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            bind();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            bind();
        }

        private void numericUpDown1_ValueChanged(object sender, EventArgs e)
        {
            timer1.Interval = (int)numericUpDown1.Value * 1000;
        }

        private void Form1_DoubleClick(object sender, EventArgs e)
        {
            textBox1.Visible = !textBox1.Visible;
            if (!textBox1.Visible && !string.IsNullOrEmpty(textBox1.Text))
            {
                AppConfig.AddUpdateAppSettings(AppConfig.DATABASE_KEY, textBox1.Text);
                _databaseConnection = AppConfig.ReadSetting(AppConfig.DATABASE_KEY);
            }
        }

        private void rbPulang_CheckedChanged(object sender, EventArgs e)
        {
            bind();
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            bindRekapharian();
        }

        private void bindRekapharian()
        {
            dataGridView1.DataSource = null;
            DataTable dt = new DataTable();
            using (var da = new SqlDataAdapter("sp_card_report_harian '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "', 'REKAP'", _databaseConnection))
            {
                da.Fill(dt);
            }
            if (dt.Rows.Count > 0)
            {
                dataGridView1.DataSource = dt;
                dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
                dataGridView1.RowHeadersVisible = false;
            }
        }

        private void dateTimePicker3_ValueChanged(object sender, EventArgs e)
        {
            bindRekapBulanan();
        }

        private void bindRekapBulanan()
        {
            dataGridView2.DataSource = null;
            DataTable dt = new DataTable();
            using (var da = new SqlDataAdapter("sp_card_report_bulanan '" + dateTimePicker3.Value.ToString("yyyy-MM-dd") + "', '" + dateTimePicker4.Value.ToString("yyyy-MM-dd") + "'", _databaseConnection))
            {
                da.Fill(dt);
            }
            if (dt.Rows.Count > 0)
            {
                dataGridView2.DataSource = dt;
                dataGridView2.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
                dataGridView2.RowHeadersVisible = false;
            }
        }

        private void chart1_Click(object sender, EventArgs e)
        {

        }

        private void populateGuruKelas()
        {
            DataTable dt = new DataTable();
            using (var da = new SqlDataAdapter("sp_card_absen_wali_kelas '" + dateTimePicker5.Value.ToString("yyyy-MM-dd") + "'", _databaseConnection))
            {
                da.Fill(dt);
            }
            if (dt.Rows.Count > 0)
            {
                tableLayoutPanel2.SuspendLayout();
                tableLayoutPanel2.ColumnStyles.Clear();
                tableLayoutPanel2.RowStyles.Clear();
                tableLayoutPanel2.Controls.Clear();

                tableLayoutPanel2.ColumnCount = dt.Columns.Count;
                tableLayoutPanel2.RowCount = 0;

                //create header
                foreach (DataColumn col in dt.Columns)
                {
                    if (dt.Columns.IndexOf(col) == 0)
                    {
                        tableLayoutPanel2.ColumnStyles.Add(new ColumnStyle(SizeType.Absolute, 55));
                    }
                    else
                    {
                        tableLayoutPanel2.ColumnStyles.Add(new ColumnStyle(SizeType.Percent, (100 / (dt.Columns.Count - 1))));
                    }
                }
                //tableLayoutPanel2.RowStyles.Add(new RowStyle(SizeType.AutoSize));
                tableLayoutPanel2.RowStyles.Add(new RowStyle(SizeType.Absolute, 30));
                int colIteration = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    tableLayoutPanel2.Controls.Add(new Label() { Text = col.ColumnName, Dock = DockStyle.Fill, TextAlign = ContentAlignment.MiddleCenter, Font = new Font(Font, FontStyle.Bold) }, colIteration, 0);
                    colIteration++;
                }

                //create row data
                foreach (DataRow row in dt.Rows)
                {
                    tableLayoutPanel2.RowCount = tableLayoutPanel2.RowCount + 1;
                    //tableLayoutPanel2.RowStyles.Add(new RowStyle(SizeType.AutoSize));
                    tableLayoutPanel2.RowStyles.Add(tableLayoutPanel2.RowCount == 1 ? new RowStyle(SizeType.Absolute, 126) : new RowStyle(SizeType.Absolute, 70));


                    colIteration = 0;
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (colIteration == 0)
                        {
                            tableLayoutPanel2.Controls.Add(new Label() { Text = row[colIteration].ToString(), Dock = DockStyle.Fill, TextAlign = ContentAlignment.TopCenter, Font = new Font(Font, FontStyle.Bold) }, colIteration, tableLayoutPanel2.RowCount);
                        }
                        else
                        {
                            Panel p = new Panel() { Dock = DockStyle.Fill, AutoScroll = true };
                            tableLayoutPanel2.Controls.Add(p);
                            string[] walikelas = row[colIteration].ToString().Split('|');
                            foreach (string guru in walikelas)
                            {
                                string[] status = guru.Split('*');
                                if (status.Length >= 4)
                                {
                                    AbsenDetail2 detail = new AbsenDetail2();
                                    detail.Nama = status[1];
                                    detail.Jam = status[2];
                                    detail.StatusKehadiran = status[3].Equals("1") ? Status.HADIR : status[3].Equals("2") ? Status.TERLAMBAT : Status.TIDAK_HADIR;
                                    detail.Foto = getFoto(status[0]);
                                    detail.Dock = DockStyle.Top;
                                    p.Controls.Add(detail);
                                }
                            }
                        }
                        colIteration++;
                    }
                }
                tableLayoutPanel2.ResumeLayout();
            }
        }

        private void populateGuruLain()
        {
            DataTable dt = new DataTable();
            using (var da = new SqlDataAdapter("sp_card_absen_pegawai '" + dateTimePicker5.Value.ToString("yyyy-MM-dd") + "'", _databaseConnection))
            {
                da.Fill(dt);
            }
            if (dt.Rows.Count > 0)
            {
                flowLayoutPanel1.Controls.Clear();
                flowLayoutPanel1.SuspendLayout();
                foreach (DataRow row in dt.Rows)
                {
                    AbsenDetail2 detail = new AbsenDetail2();
                    var status = row["absen"].ToString().Split('*');
                    detail.Nama = status[1];
                    detail.Jam = status[2];
                    detail.StatusKehadiran = status[3].Equals("1") ? Status.HADIR : status[3].Equals("2") ? Status.TERLAMBAT : Status.TIDAK_HADIR;
                    detail.Foto = getFoto(status[0]);
                    detail.Dock = DockStyle.Top;
                    detail.Width = 185;
                    flowLayoutPanel1.Controls.Add(detail);
                }
                flowLayoutPanel1.ResumeLayout();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            populateGuruKelas();
            populateGuruLain();
            _lastUpdatedPeta = DateTime.Now;
        }

        private Dictionary<string, Image> _cacheFoto = new Dictionary<string, Image>();
        private Image getFoto(string nip)
        {
            if (_cacheFoto.ContainsKey(nip))
            {
                return _cacheFoto[nip];
            }
            try
            {
                using (var daFoto = new SqlDataAdapter("sp_card_get_foto '" + nip + "'", _databaseConnection))
                {
                    var dtFoto = new DataTable();
                    daFoto.Fill(dtFoto);

                    if (dtFoto.Rows.Count > 0)
                    {
                        if (dtFoto.Rows[0]["Foto"] != DBNull.Value)
                        {
                            using (var ms = new MemoryStream((byte[])(dtFoto.Rows[0]["Foto"])))
                            {
                                Image img = Image.FromStream(ms);
                                _cacheFoto[nip] = img;
                            }
                        }
                        return _cacheFoto[nip];
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, ex.Message);
                //MessageBox.Show("Error mengambil foto : " + ex.Message);
                _cacheFoto[nip] = null;
            }            
            return null;
        }

        private void getWaktuHariIni(out DateTime? datang1, out DateTime? datang2, out DateTime? pulang1, out DateTime? pulang2)
        {
            datang1 = null;
            datang2 = null;
            pulang1 = null;
            pulang2 = null;

            using (var da = new SqlDataAdapter("select DATANG_1, DATANG_2, PULANG_1, PULANG_2 from CARD_WAKTU where HARI=dbo.GetHari(getdate())", _databaseConnection))
            {
                var dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    datang1 = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd ") + dt.Rows[0][0].ToString());
                    datang2 = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd ") + dt.Rows[0][1].ToString());
                    pulang1 = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd ") + dt.Rows[0][2].ToString());
                    pulang2 = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd ") + dt.Rows[0][3].ToString());
                }
            }
        }

        private void tabControl1_TabIndexChanged(object sender, EventArgs e)
        {

        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tabControl1.SelectedIndex == tabControl1.TabPages.IndexOf(tabPage4))
            {
                button1_Click(null, null);
            }
        }

        private void dateTimePicker5_ValueChanged(object sender, EventArgs e)
        {
            button1_Click(null, null);
        }

        DateTime? datang1;
        DateTime? datang2;
        DateTime? pulang1;
        DateTime? pulang2;
        int lastAutoSwitchedTabPageIndex = -1;

        private void timer2_Tick(object sender, EventArgs e)
        {
            try
            {
                if (!datang1.HasValue && !datang2.HasValue && !pulang1.HasValue && !pulang2.HasValue)
                {
                    getWaktuHariIni(out datang1, out datang2, out pulang1, out pulang2);
                }

                if ((int)DateTime.Now.Subtract(datang2.Value).TotalMinutes > 15 
                    && (int)DateTime.Now.Subtract(pulang1.Value).TotalMinutes < -15 
                    && lastAutoSwitchedTabPageIndex != 0)
                {
                    tabControl1.SelectedIndex = 0;
                    lastAutoSwitchedTabPageIndex = tabControl1.SelectedIndex;
                }
                else if ((int)DateTime.Now.Subtract(pulang1.Value).TotalMinutes > -15 && lastAutoSwitchedTabPageIndex != 1)
                {
                    tabControl1.SelectedIndex = 1;
                    rbPulang.Checked = true;
                    lastAutoSwitchedTabPageIndex = tabControl1.SelectedIndex;
                }
                else if ((int)DateTime.Now.Subtract(datang1.Value).TotalMinutes > -15 && (int)DateTime.Now.Subtract(datang1.Value).TotalMinutes < 0 && lastAutoSwitchedTabPageIndex != 1)
                {
                    tabControl1.SelectedIndex = 1;
                    rbDatang.Checked = true;
                    lastAutoSwitchedTabPageIndex = tabControl1.SelectedIndex;
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, ex.Message);
                //MessageBox.Show(this, ex.Message);
            }
        }

        private void timer3_Tick(object sender, EventArgs e)
        {
            if (DateTime.Now.Subtract(_lastUpdatedPeta).TotalHours > 0)
            {
                button1_Click(null, null);
            }
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            Log.Info("REPORTING APP EXITED");
        }
    }

    public static class Log
    {
        private static ILog log => LogManager.GetLogger(new StackFrame(2).GetMethod().DeclaringType);

        public static void Error(object message) => log.Error(message);

        public static void Error(Exception ex) => log.Error(ex.Message, ex);

        public static void Error(string format, params object[] param) => log.Error(string.Format(format, param));

        public static void Error(Exception ex, string format, params object[] param) => log.Error(string.Format(format, param), ex);

        public static void Info(object message) => log.Info(message);

        public static void Info(string format, params object[] param) => log.InfoFormat(format, param);

        public static void Debug(object message) => log.Debug(message);

        public static void Debug(string format, params object[] param) => log.DebugFormat(format, param);

        public static void Warn(object message) => log.Warn(message);

        public static void Warn(string format, params object[] param) => log.WarnFormat(format, param);
    }
}
